﻿using System;
using System.Collections.Generic;
using System.IO;
using CITPRO.Linq.LinqToOdbc.DialectProviders;
using CITPRO.Linq.LinqToOdbc.Tests.TestObjects;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Linq;
using System.Diagnostics;
using Ninject;
using CITPRO.Linq.LinqToOdbc.Tests.Providers;
using Remotion.Linq;

namespace CITPRO.Linq.LinqToOdbc.Tests {


    /// <summary>
    ///This is a test class for OdbcQueryFactoryTest and is intended
    ///to contain all OdbcQueryFactoryTest Unit Tests
    ///</summary>
    [TestClass()]
    public class Tests {
        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext { get; set; }
        private readonly IKernel resolver;

        public Tests() {
            resolver = new StandardKernel();
            resolver.Bind<ISqlRecorder>().To<DefaultSqlRecorder>().InSingletonScope();
            resolver.Bind<IOdbcQueryExecutor>().To<DefaultOdbcQueryExecutor>();
            resolver.Bind<IOdbcQueryContext>().To<OdbcQueryContext>();
            resolver.Bind<IOdbcReaderConvertor>().To<DefaultOdbcReaderConvertor>().InSingletonScope();
            resolver.Bind<IDialectProvider>().To<MSAccessDialectProvider>();

            resolver.Bind<IOdbcConnectionProvider>().ToMethod(m => {
                var dbpath = Path.Combine(TestContext.TestDeploymentDir, "Northwind.mdb");
                return new ConnectionProvider(string.Format(@"Driver={{Microsoft Access Driver (*.mdb)}};Dbq={0};Uid=Admin;Pwd=;", dbpath));
            });

        }


        #region Additional test attributes
        // 
        //You can use the following additional attributes as you write your tests:
        //
        //Use ClassInitialize to run code before running the first test in the class
        [ClassInitialize()]
        public static void MyClassInitialize(TestContext testContext) {


        }

        //Use ClassCleanup to run code after all tests in a class have run
        [ClassCleanup()]
        public static void MyClassCleanup() {

        }

        //Use TestInitialize to run code before running each test
        //[TestInitialize()]
        //public void MyTestInitialize()
        //{
        //}
        //
        //Use TestCleanup to run code after each test has run
        //[TestCleanup()]
        //public void MyTestCleanup()
        //{
        //}
        //
        #endregion

        [TestMethod]
        public void TestBulkExportMethod() {
            var bulkExportConfig = new BulkExportConfig(Path.GetTempFileName());
            bulkExportConfig.ColumnDelimiter = "<col>";
            bulkExportConfig.RowDelimiter = "<row>";

            resolver.Unbind<IOdbcReaderConvertor>();
            resolver.Bind<IOdbcReaderConvertor>().ToMethod(c => {
                return new BulkExportReaderConvertor(bulkExportConfig);
            });
            using (var ctx = resolver.Get<IOdbcQueryContext>()) {
                var query = from e in ctx.Queryable<Employee>()
                            select new { e.EmployeeID, e.FirstName, e.LastName };
                query.ToList();
            }
            Assert.AreEqual(10, bulkExportConfig.AffectedRecords);
            Assert.AreEqual(ExpectedResults.BulkExportTest, File.ReadAllText(bulkExportConfig.ExportFilePath));
            File.Delete(bulkExportConfig.ExportFilePath);
        }


        [TestMethod]
        public void TestRowHandler() {
            var readerConvertor = resolver.Get<IOdbcReaderConvertor>();
            var rowCount = 0;
            readerConvertor.SetRowReadHandler(typeof(Employee), d => {
                rowCount++;
                return false;  // should not cache into memory
            });
            using (var ctx = resolver.Get<IOdbcQueryContext>()) {
                var query = from e in ctx.Queryable<Employee>()
                            where e.EmployeeID == 1
                            select e;
                query.ToList();
            }
            Assert.AreEqual(1, rowCount);
            readerConvertor.ClearAllRowReadHandlers();
        }


        [TestMethod]
        public void TestMultipleQueryOnOneConnection() {
            var result = new List<Employee>();
            using (var ctx = resolver.Get<IOdbcQueryContext>()) {
                var query1 = (from e in ctx.Queryable<Employee>()
                              where e.EmployeeID == 1
                              select e).ToList();
                result.AddRange(query1);
                var query2 = (from e in ctx.Queryable<Employee>()
                              where e.EmployeeID == 2
                              select e).ToList();
                result.AddRange(query2);
            }
            Assert.AreEqual(2, result.Count);
        }


        [TestMethod]
        public void TestInClauseString() {
            int affected;
            var recorder = resolver.Get<ISqlRecorder>();
            using (var ctx = resolver.Get<IOdbcQueryContext>()) {
                var query = from p in ctx.Queryable<Employee>()
                            where new[] { "Nancy", "Margaret", "Steven" }.Contains(p.FirstName)
                            select new { p.FirstName, p.LastName };
                var result = query.ToList();
                affected = result.Count;
            }
            Assert.AreEqual(ExpectedResults.TestInClauseString, recorder.LastRecordedSql);
            Assert.AreEqual(3, affected);
        }

        [TestMethod]
        public void TestInClauseValue() {
            var result = new List<Employee>();
            var recorder = resolver.Get<ISqlRecorder>();
            using (var ctx = resolver.Get<IOdbcQueryContext>()) {
                var query = from p in ctx.Queryable<Employee>()
                            where new[] { 1, 2, 3 }.Contains(p.EmployeeID)
                            select p;
                result.AddRange(query.ToList());
            }
            Assert.AreEqual(ExpectedResults.InClauseTest, recorder.LastRecordedSql);
            Assert.AreEqual(3, result.Count);
        }

        [TestMethod]
        public void TestNotInClause() {
            var result = new List<Employee>();
            var recorder = resolver.Get<ISqlRecorder>();
            using (var ctx = resolver.Get<IOdbcQueryContext>()) {
                var array = new[] { 1, 2, 3 };
                var query = from p in ctx.Queryable<Employee>()
                            where !array.Contains(p.EmployeeID)
                            select p;
                result.AddRange(query.ToList());
            }
            Assert.AreEqual(ExpectedResults.NotInClauseTest, recorder.LastRecordedSql);
            Assert.AreEqual(7, result.Count);
        }

        [TestMethod]
        public void BasicSqlGenerationTest() {
            var result = new List<Employee>();
            var recorder = resolver.Get<ISqlRecorder>();
            using (var ctx = resolver.Get<IOdbcQueryContext>()) {
                var query = from p in ctx.Queryable<Employee>()
                            where p.FirstName == "Nancy" && p.LastName == "Davolio"
                            select p;
                result.AddRange(query.ToList());
            }
            Assert.AreEqual(ExpectedResults.BasicSqlGenerationTest, recorder.LastRecordedSql);
            Assert.AreEqual(1, result.Count);
        }

        [TestMethod]
        public void SelectClauseConstantTest() {
            int recordCount;
            var recorder = resolver.Get<ISqlRecorder>();
            using (var ctx = resolver.Get<IOdbcQueryContext>()) {
                var query = from p in ctx.Queryable<Employee>()
                            where p.FirstName == "Nancy" && p.LastName == "Davolio"
                            select new {
                                p.FirstName,
                                p.LastName,
                                SomeNumber = 0
                            };
                var result = query.ToList();
                recordCount = result.Count;
            }
            Assert.AreEqual(ExpectedResults.SelectClauseContantTest, recorder.LastRecordedSql);
            Assert.AreEqual(1, recordCount);
        }

        [TestMethod]
        public void NewClauseTest() {
            List<string> result;
            var recorder = resolver.Get<ISqlRecorder>();
            using (var ctx = resolver.Get<IOdbcQueryContext>()) {
                var query = from p in ctx.Queryable<Employee>()
                            where p.FirstName == "Nancy" && p.LastName == "Davolio"
                            select new { p.FirstName };
                result = query.ToList().Select(s => s.FirstName).ToList();
            }
            Assert.AreEqual(ExpectedResults.NewClauseTest, recorder.LastRecordedSql);
            Assert.AreEqual(1, result.Count);
        }

        [TestMethod]
        public void IsNullTest() {
            List<string> result;
            var recorder = resolver.Get<ISqlRecorder>();
            using (var ctx = resolver.Get<IOdbcQueryContext>()) {
                var query = from p in ctx.Queryable<Employee>()
                            where p.FirstName == null
                            select new { p.FirstName };
                result = query.ToList().Select(s => s.FirstName).ToList();
            }
            Assert.AreEqual(ExpectedResults.IsNullTest, recorder.LastRecordedSql);
            Assert.AreEqual(1, result.Count);
        }

        [TestMethod]
        public void IsNotNullTest() {
            List<string> result;
            var recorder = resolver.Get<ISqlRecorder>();
            using (var ctx = resolver.Get<IOdbcQueryContext>()) {
                var query = from p in ctx.Queryable<Employee>()
                            where p.FirstName != null
                            select new { p.FirstName };
                result = query.ToList().Select(s => s.FirstName).ToList();
            }
            Assert.AreEqual(ExpectedResults.IsNotNullTest, recorder.LastRecordedSql);
            Assert.AreEqual(9, result.Count);
        }

        [TestMethod]
        public void ValueTest() {
            List<string> result;
            var recorder = resolver.Get<ISqlRecorder>();
            using (var ctx = resolver.Get<IOdbcQueryContext>()) {
                var query = from p in ctx.Queryable<Employee>()
                            where p.FirstName == "Nancy" && p.EmployeeID > 0 && p.EmployeeID < 10
                            select new { p.FirstName, p.LastName };
                result = query.ToList().Select(s => s.FirstName + " " + s.LastName).ToList();
            }
            Assert.AreEqual(ExpectedResults.ValueTest, recorder.LastRecordedSql);
            Assert.AreEqual(1, result.Count);
        }

        [TestMethod]
        public void DateCompare_Access() {
            List<string> result;
            DateTime? targetDate = new DateTime(1963, 1, 1);
            var recorder = resolver.Get<ISqlRecorder>();
            using (var ctx = resolver.Get<IOdbcQueryContext>()) {
                var query = from p in ctx.Queryable<Employee>()
                            where p.BirthDate >= targetDate
                            select new { p.FirstName, p.LastName };
                result = query.ToList().Select(s => s.FirstName + " " + s.LastName).ToList();
            }
            Assert.AreEqual(3, result.Count);
            Assert.AreEqual(ExpectedResults.DateCompare_Access, recorder.LastRecordedSql);
        }

        [TestMethod]
        public void DateCompare_LeagacyOdbc() {
            List<string> result;
            resolver.Unbind<IDialectProvider>();
            resolver.Bind<IDialectProvider>().To<LegacyODBCDialectProvider>();
            var recorder = resolver.Get<ISqlRecorder>();
            using (var ctx = resolver.Get<IOdbcQueryContext>()) {
                var query = from p in ctx.Queryable<Employee>()
                            where p.BirthDate >= new DateTime(1963, 1, 1)
                            select new { p.FirstName, p.LastName };
                result = query.ToList().Select(s => s.FirstName + " " + s.LastName).ToList();
            }
            Assert.AreEqual(3, result.Count);
            Assert.AreEqual(ExpectedResults.DateCompare_LegacyODBC, recorder.LastRecordedSql);
        }

        [TestMethod]
        public void QueryNullIntTest() {
            List<Employee> result;
            //var recorder = resolver.Get<ISqlRecorder>();
            using (var ctx = resolver.Get<IOdbcQueryContext>()) {
                var query = from p in ctx.Queryable<Employee>()
                            where p.ReportsTo == null
                            select p;
                result = query.ToList();
            }
            Assert.AreEqual(2, result.Count);
        }




    }
}
